package Yangtz.Cs.Service;

import Yangtz.Cs.DAO.BorrowDao;
import Yangtz.Cs.DAO.ReaderDao;
import Yangtz.Cs.Entity.BorrowBean;
import Yangtz.Cs.Utils.DataTransmissionUtil;
import Yangtz.Cs.Utils.JDBCUtil;
import Yangtz.Cs.Utils.PlusDay;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class BorrowService {
    public boolean insert(BorrowBean borrowBean) {
        return new BorrowDao().insert(borrowBean);
    }

    public ArrayList<BorrowBean> findAll() {
        return new BorrowDao().findAll();
    }

    public ArrayList<BorrowBean> find(String id) {
        return new BorrowDao().find(id);
    }

    public boolean delete(String id) {
        return new BorrowDao().delete(id);
    }

    public boolean update(BorrowBean borrowBean) {
        return new BorrowDao().update(borrowBean);
    }

    public void borrowInfo(String rdID, HttpServletResponse response) throws IOException {

        JSONArray jsonArray = new JSONArray();

        //拿到指定rdID所有的借书对象
        ArrayList<BorrowBean> borrowList = new BorrowDao().find(rdID);

        //将其封装为数组格式,然后重新赋值给jsonarray，不然jsonarray就是空的
        jsonArray = jsonArray.fromObject(borrowList);

        DataTransmissionUtil.sendJsonArrayToAndroid(jsonArray, response);
    }

    public void UpdateBkStatus(BorrowBean borrowBean, HttpServletResponse response) {

        JSONObject jsonObject = new JSONObject();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {


            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String string = sdf.format(new Date().getTime());

            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            String sql1 = "update tb_borrow set lsHasReturn = " +
                    "'已还',ldDateRetAct = '" + string + "' where rdID = '" + borrowBean.getRdID() +
                    "' and bkID = '" + borrowBean.getBkID() + "'";

            String sql2 = "update tb_book set bkStatus = " +
                    "'在馆' where" +
                    " bkID = '" + borrowBean.getBkID() + "'";

            String sql3 = "create trigger t1 after update"
                    + " on tb_borrow for each row begin " +
                    "if(old.ldDateRetAct!=new.ldDateRetAct)" +
                    "then " +
                    "update tb_reader set rdBorrowQty=rdBorrowQty-1 where rdID="
                     + "'" + borrowBean.getRdID() + "';" +
                    "end if;" +
                    "end;";


            System.out.println(sql3);

            //要先执行这条语句
            stmt.executeUpdate(sql3);
            int num1 = stmt.executeUpdate(sql1);
            int num2 = stmt.executeUpdate(sql2);
            //创建完触发事件后要及时删除该触发器，不然会报错
            stmt.executeUpdate("drop trigger t1");
            if ((num1 > 0) && (num2 > 0)) {
                jsonObject.put("ret_tag", "success");
                DataTransmissionUtil.sendJsonToAndroid(jsonObject, response);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void borrow(BorrowBean borrowBean, HttpServletResponse response) {
        JSONArray jsonArray = new JSONArray();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int canLendDay=0;

        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();

            //第一步，根据rdID找到rdType，再根据rdType找到可借天数
            int rdType=new ReaderDao().find(borrowBean.getRdID()).getRdType();
            //第二步，根据rdType找到可借天数
            String sql1 = "select canLendDay from tb_readertype where rdtype =" + rdType;
            rs = stmt.executeQuery(sql1);
            while (rs.next()){
                canLendDay =rs.getInt(1);
            }


            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date=new Date();
            String ldDateOut = sdf.format(date.getTime());
            String ldDatePlan=new PlusDay().plusDay(canLendDay,date);



            //将book改为不在馆
            String sql2 = "update tb_book set bkStatus = " +
                    "'不在馆' where" +
                    " bkID = '" + borrowBean.getBkID() + "'";

            //插入borrow表
            String sql3="insert into tb_borrow values('"+borrowBean.getRdID()+
                    "','"+borrowBean.getBkID()+
                    "','"+ldDateOut+
                    "','"+ldDatePlan +
                    "','0000-00-00','未还')";


            String sql4 = "create trigger t2 after insert"
                    + " on tb_borrow for each row"
                    + " update tb_reader set rdBorrowQty=rdBorrowQty+1"
                    + " where rdID=" + "'" + borrowBean.getRdID() + "'" + ";";

            System.out.println(sql3);

            stmt.executeUpdate(sql4);
            int num1=stmt.executeUpdate(sql2);
            int num2=stmt.executeUpdate(sql3);
            stmt.executeUpdate("drop trigger t2");

            if((num1>0)&&(num2>0)){
                //拿到指定rdID所有的借书对象
                ArrayList<BorrowBean> borrowList = new BorrowDao().find(borrowBean.getRdID());

                //将其封装为数组格式,然后重新赋值给jsonarray，不然jsonarray就是空的
                jsonArray = jsonArray.fromObject(borrowList);

                DataTransmissionUtil.sendJsonArrayToAndroid(jsonArray, response);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }


    }
}
